<?php
/**
 * Ordering repository
 */
namespace BWL\webshopBundle\Repository;

use Doctrine\ORM\EntityRepository;
use BWL\webshopBundle\Entity\Item;

/**
 * OrderingRepository
 *
 * Ordering specific methods to locale entity.
 */
class OrderingRepository extends EntityRepository
{

  /**
   * Returns all orderings without baskets
   * @param null $user
   * @return array
   */
  public function findOrderings($user = null) {
    $qb = $this->findOrderingQueryBuilder($user, 1);
    return $qb->getQuery()->getResult();
  }

  /**
   * Returns last Basket if exists, otherwise null
   * @param null $user
   * @return mixed
   */
  public function findBasket($user = null) {
    $qb = $this->findOrderingQueryBuilder($user);
    return $qb->getQuery()->getOneOrNullResult();
  }

  /**
   * Returns last Baskets
   * @param null $user
   * @return array
   */
  public function findBaskets($user = null) {
    $qb = $this->findOrderingQueryBuilder($user);
    return $qb->getQuery()->getResult();
  }

  // --- Helper ---------------------------------------------------------------

  /**
   * Returns last Baskets QueryBuilder
   * @param null $user
   * @param null $type
   * @return \Doctrine\ORM\QueryBuilder
   */
  private function findOrderingQueryBuilder($user = null, $type = null) {
    $qb = $this->createQueryBuilder('o')
      ->select('o');
    $qb->where('o.type = ?1');

    if(false === is_null($user)) {
      $qb->andWhere('o.user = ?2');
      $qb->setParameters(array(1 => (is_null($type) ? 0 : 1), 2 => $user));
    } else {
      $qb->setParameters(array(1 => (is_null($type) ? 0 : 1)));
    }

    return $qb;
  }

  /**
   * Returns all ordering-components of a given period.
   * The period relates to ordered date.
   * @param $from \DateTime ordered date
   * @param $to \DateTime ordered date
   * @return array
   */
  public function findOrderingComponentsByPeriod(\DateTime $from, \DateTime $to) {
    $qb = $this->createQueryBuilder('o');
    $qb->select('i.id AS item_id',
      'SUM(o.count) AS amount',
      'o.orderDate AS ordered',
      'o.shipmentDate AS dispatched',
      'SUBSTRING(o.orderDate, 1, 10) AS ordered_date_string',
      'SUBSTRING(o.shipmentDate, 1, 10) AS dispatched_date_string');
    $qb->leftJoin('o.item', 'i');
    $qb->where('o.orderDate >= ?1');
    $qb->andWhere('o.orderDate < ?2');;
    $qb->groupBy('ordered_date_string');
    $qb->addGroupBy('dispatched_date_string');
    $qb->addGroupBy('i.id');
    $qb->orderBy('o.shipmentDate', 'ASC');
    $qb->addOrderBy('o.orderDate', 'ASC');
    $qb->addOrderBy('i.name', 'ASC');

    $qb->setParameters(array(
      1 => $from,
      2 => $to,
    ));

    return $qb->getQuery()->getResult();
  }


}